1. Field of the Invention
This invention relates to database systems, and, in particular, to a system and method for performing a “delete all rows” operation on a database table or other database object.
2. Description of the Related Art
In database systems, a “transaction” refers to an atomic set of operations performed against a database, which may access, create, modify or delete database data or metadata. A “commit” occurs when the transaction has completed its processing and any changes to the database by the transaction are ready to be permanently implemented in the database system. Transaction log records can be maintained in a database system to allow data recovery in the event of an error, that may include hardware failure, network failure, process failure, database instance failure, data access conflicts, user errors, and statement failures in database access programs.
Various types of transaction log records can be maintained in a database system for data recovery. One type of log record that may be maintained is the “undo” record. Undo records contain information about changes that were introduced into the database system. For example, if a row in a table were modified, the changes will be stored in the undo record identifying the block of the database system that includes the modified table row.
Storing undo records provides for various additional features of the database system, such as a “flashback” feature and a “consistent read” feature. Flashback allows a user to retrieve older data from a database table, for example, to compare against a current version or to aid in troubleshooting. Consistent read ensures that long-running transactions will receive consistent versions of the data, even if the data has been modified during the transaction.
Because the undo tablespace is of limited size, undo records cannot be stored indefinitely. Therefore, database administrators may set a parameter known as “undo retention,” which specifies how long undo records should be maintained. Flashback is guaranteed to produce an older version of the data as long as the data are within the undo retention time. Similarly, consistent read is guaranteed to produce consistent data as long as the read operation does not exceed the undo retention time.
Database tables may be separated into partitions, each of which represents a logical collection of the data within the table. For example, a table holding financial information may be separated into partitions representing fiscal quarters. Database tables, partitions of database tables, and other related objects may be collectively referred to as “database objects.”
A database administrator may wish to delete all the information in a particular database object. For example, a database administrator may wish to delete all the financial information for the last fiscal quarter, in order create space to store financial data for an upcoming fiscal quarter. Performing a “delete all rows” operation, also referred to as a “bulk delete” operation, typically involves placing information for each row into the undo tablespace so that the information can be retrieved for a consistent read or a flashback. The runtime of the bulk delete operation is therefore linear with respect to the number of rows in the database object. The amount of undo tablespace used is also linear with respect to the number of rows in the database object. For large database objects, performing a bulk delete can be a time-consuming operation.
In order to find a lower latency solution, many database administrators choose to perform a “truncate” operation instead of a bulk delete. The truncate operation releases the tablespace in the database object without writing any information to the undo tablespace. Because the truncation operation does not store undo information for the deleted rows, the runtime is constant. However, when the truncate operation is used in place of the bulk delete operation, neither consistent reads nor flashbacks can be provided for the deleted data.
There is therefore a need in the art for a bulk delete operation with reduced latency that allows for retrieval of the deleted data.